package Dm

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"strconv"
	"strings"
	"time"
)

type definitionConvertS struct {
	Name     string
	Interval global.JobResultDefinition
	//Body     JobConvertBody
}

func dbaSchedulerJobsIf(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var f1 any
	var event = "[oracleDbaSchedulerJobsIf]"
	for _, v := range []string{"ALL_SCHEDULER_JOBS", "DBA_SCHEDULER_JOBS"} {
		result.BaseResult.Sql = fmt.Sprintf("select count(*) as \"sum\" from %v where owner = '%s'  and JOB_NAME = '%s'", v, s.TableInfo.Schema, s.TableInfo.Table)
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil {
			continue
		}
		if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
			result.Result = true
		}
		break
	}

	return
}
func dbaJobsIf(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var f1 any
	var event = "[oracleDbaJobsIf]"
	for _, v := range []string{
		"ALL_JOBS", "DBA_JOBS",
	} {
		result.BaseResult.Sql = fmt.Sprintf("select count(*) as \"sum\" from %v where 1=1 and SCHEMA_USER = '%v' and JOB = '%v'", v, s.TableInfo.Schema, s.TableInfo.Table)
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil {
			continue
		}
		if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
			result.Result = true
		}
		break
	}

	return
}
func (or TableInfoMeta) JobExist(s global.TablesMetaInfoInput) (res global.ObjectExistReturnResult, err error) {
	if res, err = dbaSchedulerJobsIf(s); err == nil && res.Result {
		return res, nil
	}
	if res, err = dbaJobsIf(s); err != nil {
		return res, err
	}
	return res, err
}

func (or TableInfoMeta) JobName(s global.TablesMetaInfoInput) (result global.ObjectNameReturnResult, err error) {
	var (
		f1    any
		event = "[oracleJobName]"
	)
	for _, v := range []string{
		fmt.Sprintf("select JOB_NAME FROM DBA_SCHEDULER_JOBS where owner = '%s' %v", s.TableInfo.Schema,
			func() string {
				switch {
				case s.TableInfo.Table == "*":
					return ""
				case strings.Contains(s.TableInfo.Table, "%"):
					return fmt.Sprintf(" AND JOB_NAME like '%%%v%%'", s.TableInfo.Table)
				default:
					return fmt.Sprintf(" AND JOB_NAME = '%v'", s.TableInfo.Table)
				}
			}()),
		fmt.Sprintf("select JOB FROM DBA_JOBS where 1=1 and SCHEMA_USER = '%v' %v", s.TableInfo.Schema,
			func() string {
				switch {
				case s.TableInfo.Table == "*":
					return ""
				case strings.Contains(s.TableInfo.Table, "%"):
					return fmt.Sprintf(" AND JOB like '%v'", s.TableInfo.Table)
				default:
					return fmt.Sprintf(" AND JOB = '%v'", s.TableInfo.Table)
				}
			}()),
	} {
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: v,
			Expect:       ETL.StringGarth,
		}); err != nil || f1 == nil {
			continue
		}
		result.Result = append(result.Result, f1.([]string)...)
	}
	return
}

func utc(s string) (res string) {
	res = s
	if n := strings.Index(s, "+0800"); n != -1 {
		return strings.TrimSpace(s[:n])
	}
	return
}

/*
在 Oracle 的 `DBMS_SCHEDULER` 中，除了 `BYMONTHDAY`、`BYMINUTE` 和 `BYSECOND`，还有许多其他参数可以用于定义调度频率。以下是一些常用的参数：

### 常用参数
1. **`FREQ`**：定义调度的频率。常见的值包括：
  - `SECONDLY`：每秒执行。
  - `MINUTELY`：每分钟执行。
  - `HOURLY`：每小时执行。
  - `DAILY`：每日执行。
  - `WEEKLY`：每周执行。
  - `MONTHLY`：每月执行。
  - `YEARLY`：每年执行。

2. **`BYHOUR`**：指定小时（0-23），适用于 `DAILY`、`WEEKLY` 和 `MONTHLY`。
3. **`BYDAY`**：指定星期几（如 `MON`、`TUE` 等），适用于 `WEEKLY` 和 `MONTHLY`。
4. **`BYMONTH`**：指定月份（1-12），适用于 `YEARLY` 和 `MONTHLY`。
5. **`BYSETPOS`**：指定在给定的频率内选择特定的实例。例如，`BYSETPOS=1` 表示第一个，`-1` 表示最后一个。
6. **`INTERVAL`**：指定调度的间隔。例如，`INTERVAL=2` 表示每2个单位执行一次。
### 示例
以下是一个示例，结合不同的参数：

```sql
repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,WED,FRI; BYHOUR=15; BYMINUTE=0; BYSECOND=0'
`
这个设置表示作业将在每周的星期一、星期三和星期五的下午3点执行。
*/
func frequencyVal(s string) global.ScheduleType {
	var (
		res string
	)
	if strings.HasPrefix(s, "FREQ") && strings.Contains(s, "=") {
		res = strings.ToUpper(strings.TrimSpace(strings.Split(s, "=")[1]))
	}
	switch res {
	case "YEARLY":
		return global.Yearly
	case "MONTHLY":
		return global.Monthly
	case "WEEKLY":
		return global.Weekly
	case "DAYLY":
		return global.Daily
	case "HOURLY":
		return global.Hourly
	case "MINUTELY":
		return global.Minutely
	case "SECONDLY":
		return global.Secondly
	default:
		return global.Yearly
	}
}
func stringToSliceInt(s string) (res []int, err error) {
	for _, x := range strings.Split(strings.Split(s, "=")[1], ",") {
		var r int
		if r, err = strconv.Atoi(x); err != nil {
			return
		}
		res = append(res, r)
	}
	return
}

func schedulerPrint(v global.JobResultDefinition) string {
	return fmt.Sprintf("BEGIN\n    "+
		"DBMS_SCHEDULER.CREATE_JOB (\n"+
		"        job_name        => '%v',\n"+
		"        job_type        => '%v',\n"+
		"        job_action      => '%v',\n"+
		"        start_date      => TO_TIMESTAMP_TZ('%v', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'), \n"+
		"        repeat_interval  => '%v', \n"+
		"        enabled         => %v\n"+
		"    );\n"+
		"END;\n"+
		"/\n",
		v.Name,
		v.Type,
		v.Action,
		strings.ReplaceAll(v.StartTime, "+0800 ", "+"),
		v.Interval,
		v.Enable)
}
func jobPrint(body, startTime, interval string) string {
	return fmt.Sprintf("declare jobno number; \n"+
		"BEGIN\n    "+
		"DBMS_JOB.SUBMIT (\n"+
		"        job        => jobno,\n"+
		"        what      => '%v',\n"+
		"        next_date      => TO_DATE('%v', 'YYYY-MM-DD HH24:MI:SS'), \n"+
		"        interval  => '%v' \n"+
		"    );\n"+
		"END;\n"+
		"/\n",
		body,
		utc(startTime),
		interval)
}

func findFirstDifference(str1, str2 string) int {
	minLength := len(str1)
	if len(str2) < minLength {
		minLength = len(str2)
	}
	for i := 0; i < minLength; i++ {
		if str1[i] != str2[i] {
			return i // Return the index of the first difference
		}
	}
	if len(str1) != len(str2) {
		return minLength // If one string is longer, the difference is at the end of the shorter string
	}
	return -1 // Return -1 if the strings are identical
}
func getMonthToInt(s time.Month) int {
	switch s {
	case time.January:
		return 1
	case time.February:
		return 2
	case time.March:
		return 3
	case time.April:
		return 4
	case time.May:
		return 5
	case time.June:
		return 6
	case time.July:
		return 7
	case time.August:
		return 8
	case time.September:
		return 9
	case time.October:
		return 10
	case time.November:
		return 11
	case time.December:
		return 12
	default:
		return 0
	}
}
func getWeekToInt(weekday time.Weekday) int {
	switch weekday {
	case time.Sunday:
		return 7
	case time.Monday:
		return 1
	case time.Tuesday:
		return 2
	case time.Wednesday:
		return 3
	case time.Thursday:
		return 4
	case time.Friday:
		return 5
	case time.Saturday:
		return 6
	default:
		return 0
	}
}
func getDateStringToInt(dateSplit []string) (dateInt []int) {
	dateInt = make([]int, 3)
	for k, v := range dateSplit {
		if val, err := strconv.Atoi(strings.TrimSpace(v)); err != nil {
			return []int{}
		} else {
			dateInt[k] = val
		}
	}
	return
}

func yearMonthDayCompareSlices(int1, int2 []int) string {
	for k, v := range global.CompareSlices(int1, int2) {
		if v == -1 {
			switch k {
			case 0:
				return "year"
			case 1:
				return "month"
			case 2:
				return "day"
			}
		}
	}
	return ""
}
func hourMinuteSecondCompareSlices(timeSplit []string) string {
	for k, v := range timeSplit {
		if v == "00" {
			switch k {
			case 0:
				return "hour"
			case 1:
				return "minute"
			case 2:
				return "second"
			}
			break
		}
	}
	return ""
}
func getTruncTimeStampTypePoint(timeStr string) string {
	var datePoint, timePoint string
	year, month, day := time.Now().Date()
	dateTimeSplit := strings.Split(strings.TrimSpace(timeStr), " ")
	timePoint = hourMinuteSecondCompareSlices(strings.Split(strings.TrimSpace(dateTimeSplit[1]), ":"))
	datePoint = yearMonthDayCompareSlices([]int{year, getMonthToInt(month), day}, getDateStringToInt(strings.Split(strings.TrimSpace(dateTimeSplit[0]), "-")))
	if len(datePoint) > 0 {
		return datePoint
	}
	return timePoint
}
func getTruncTimeStampT(trunc, timeStr string) (res string) {
	dateTimeSplit := strings.Split(strings.TrimSpace(timeStr), " ")
	dateSplit := dateTimeSplit[0]
	timeSplit := dateTimeSplit[1]
	switch trunc {
	case "year", "month", "day":
		var q []string
		var re = strings.Split(dateSplit, "-")
		switch trunc {
		case "year":
			q = append(q, re[:1]...)
		case "month":
			q = append(q, re[:2]...)
		default:
			q = re
		}
		switch len(q) {
		case 1:
			res = fmt.Sprintf("%v-01-01 00:00:00", strings.Join(q, "-"))
		case 2:
			res = fmt.Sprintf("%v-01 00:00:00", strings.Join(q, "-"))
		case 3:
			res = fmt.Sprintf("%v 00:00:00", strings.Join(q, "-"))
		}
	case "hour", "minute", "second":
		var q []string
		var re = strings.Split(timeSplit, ":")
		switch trunc {
		case "minute":
			q = append(q, re[:1]...)
		case "second":
			q = append(q, re[:2]...)
		default:
			q = re
		}
		switch len(q) {
		case 1:
			res = fmt.Sprintf("%v %v:00:00", dateSplit, strings.Join(q, ":"))
		case 2:
			res = fmt.Sprintf("%v %v:00", dateSplit, strings.Join(q, ":"))
		case 3:
			res = timeStr
		}
	default:
		res = timeStr
	}
	return
}

func dbmsJob(s global.SchemaObjectInput) (res global.JobResultDefinition, err error) {
	var f1 any
	var event = "[oracleDbmsJob]"
	ExecSql := fmt.Sprintf("SELECT \"JOB\" as \"jobName\",\n"+
		"\"WHAT\" as \"jobDefinition\", \n"+
		"\"INTERVAL\" as \"interval\",\"LAST_DATE\" as \"lastDate\",\"NEXT_DATE\" as \"nextDate\" \n"+
		"FROM dba_jobs "+
		"where 1=1 and SCHEMA_USER = '%v' and JOB = '%v'", s.Schema, s.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: ExecSql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		return
	}
	if err = global.MapSubToStructSubJsonName(f1.(map[string]any), &res); err != nil {
		return
	}
	res.Name = fmt.Sprintf("dbms_job_%v", res.Name)
	res.JobPackage = "DBMS_JOB.SUBMIT"
	return
}

// 新包DBMS_SCHEDULER.CREATE_JOB的用法
func dbmsScheduler(s global.SchemaObjectInput) (res global.JobResultDefinition, err error) {
	var f1 any
	var event = "[oracleDbmsScheduler]"
	ExecSql := fmt.Sprintf("SELECT \"JOB_NAME\" as \"jobName\",\"JOB_TYPE\" as \"jobType\", \n"+
		"\"JOB_ACTION\" as \"jobDefinition\",\"START_DATE\" as \"startDate\",\"LAST_START_DATE\" as \"lastDate\",\"NEXT_RUN_DATE\" as \"nextDate\", \n"+
		"\"REPEAT_INTERVAL\" as \"interval\",\"ENABLED\" as \"enable\",\"COMMENTS\" as \"comment\" \n"+
		"FROM dba_scheduler_jobs "+
		"where 1=1 and OWNER = '%v' and JOB_NAME='%v' ", s.Schema, s.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: ExecSql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		return
	}
	if err = global.MapSubToStructSubJsonName(f1.(map[string]any), &res); err != nil {
		return
	}
	res.PrintInfo = schedulerPrint(res)
	res.JobPackage = "DBMS_SCHEDULER.CREATE_JOB"
	return
}
func intervalVal(v string) (res int, err error) {
	if strings.HasPrefix(v, "INTERVAL") && strings.Contains(v, "=") {
		if res, err = strconv.Atoi(strings.Split(v, "=")[1]); err != nil {
			return
		}
	}
	return
}
func intervalC(s global.JobResultDefinition) (res global.JobInterval, err error) {
	var i = strings.Split(s.Interval, ";")
	if len(i) == 2 {
		if strings.HasPrefix(strings.ToUpper(strings.TrimSpace(i[0])), "FREQ") && strings.HasPrefix(strings.ToUpper(strings.TrimSpace(i[1])), "INTERVAL") {
			if res.Interval, err = intervalVal(strings.ToUpper(strings.TrimSpace(i[1]))); err != nil {
				return
			}
			res.Frequency = frequencyVal(strings.ToUpper(strings.TrimSpace(i[0])))
			return
		}
	}
	for _, v := range i {
		if strings.HasPrefix(v, "FREQ") && strings.Contains(v, "=") {
			res.Frequency = frequencyVal(strings.Split(v, "=")[1])
		}
		if strings.HasPrefix(v, "BYMONTH") && strings.Contains(v, "=") {
			if res.ByMonth, err = stringToSliceInt(v); err != nil {
				return
			}
		}
		if strings.HasPrefix(v, "BYDAY") && strings.Contains(v, "=") {
			if res.ByMonthDay, err = stringToSliceInt(v); err != nil {
				return
			}
		}
		if strings.HasPrefix(v, "BYHOUR") && strings.Contains(v, "=") {
			if res.ByHour, err = stringToSliceInt(v); err != nil {
				return
			}
		}
		if strings.HasPrefix(v, "BYMINUTE") && strings.Contains(v, "=") {
			if res.ByMinute, err = stringToSliceInt(v); err != nil {
				return
			}
		}
		if strings.HasPrefix(v, "BYSECOND") && strings.Contains(v, "=") {
			if res.BySecond, err = stringToSliceInt(v); err != nil {
				return
			}
		}
	}
	return
}

func dbmsJobIntervalConvert(s global.SchemaObjectInput, p global.JobResultDefinition) (res global.JobDefinitionConvertS, err error) {
	var (
		t                      map[string]string
		lastTime, intervalTime string
	)
	if t, err = dynamicSql(s, p.Interval); err != nil {
		return
	}
	for k, v := range t {
		if strings.EqualFold(k, "interval") {
			intervalTime = utc(v)
		}
		if strings.EqualFold(k, "nowTime") {
			lastTime = utc(v)
		}
	}
	if res.Interval.TimeFrequency, err = timeDifference(getTruncTimeStampT(getTruncTimeStampTypePoint(intervalTime), lastTime), intervalTime); err != nil {
		return
	}
	res.StartTime = intervalTime
	return
}
func jobBodyTrimBeginEnd(s string) (r string) {
	r = strings.TrimSpace(s)
	if strings.HasPrefix(strings.ToLower(r), "begin") {
		r = r[len("begin"):]
	}
	if strings.HasSuffix(strings.ToLower(r), "end;") {
		r = r[:len(r)-len("end;")]
	}
	return
}
func jobBodyFuncProcIf(event, body string, s global.SchemaObjectInput) (res bool, err error) {
	return procFuncExist(event, funcProcTrim(body), s)
}
func jobBodyDmlIf(body string) (res bool) {
	if strings.HasPrefix(body, "--") {
		if n := strings.Index(body, "\n"); n != -1 {
			body = body[n:]
		}
	}
	if strings.HasPrefix(body, "/*") {
		if n := strings.Index(body, "*/"); n != -1 {
			body = strings.TrimSpace(body[n+2:])
		}
	}
	for _, v := range []string{"insert", "update", "delete", "select"} {
		if strings.HasPrefix(strings.ToLower(strings.TrimSpace(body)), v) {
			return true
		}
	}
	return
}
func jobBodyDblinkIf(body string, s global.SchemaObjectInput) (res bool, err error) {
	if res, err = useLocalDblinkExist(body, s); err != nil {
		return
	}
	return
}
func jobBodyDbmsPackage(body string) (res bool) {
	if strings.HasPrefix(strings.ToLower(strings.TrimSpace(body)), "dbms_") {
		return true
	}
	return
}

func jobBodyPrepareActive(event, body string, s global.SchemaObjectInput) (res []global.JobBodyPrepareS) {
	var (
		status bool
		err    error
	)
	p := jobBodyTrimBeginEnd(body)
	for _, v := range strings.Split(p, ";") {
		t := strings.TrimSpace(strings.ReplaceAll(v, "commit", ""))
		t = strings.TrimSpace(jobBodyTrimBeginEnd(t))
		if len(t) == 0 {
			continue
		}
		var re = global.JobBodyPrepareS{ExecType: "normal", BodyOld: t}
		if jobBodyDmlIf(t) {
			//stmt, _ := tableNameParse(t)
			//for _, t1 := range stmt {
			//	if !strings.Contains(t1, "@") {
			//		continue
			//	}
			//	re.ExecType = "dblink"
			//	if status, err = jobBodyDblinkIf(t1, s); err != nil {
			//		continue
			//	}
			//	if status {
			//		re.BodyModify = strings.ReplaceAll(t, t1, strings.Split(t1, "@")[0])
			//	}
			//}
			res = append(res, re)
			continue
		}
		re.ExecType = "dbms_package"
		if jobBodyDbmsPackage(t) {
			res = append(res, re)
			continue
		}
		if status, err = jobBodyFuncProcIf(event, t, s); err != nil {
			continue
		}
		if status {
			re.ExecType = "func/proc"
			re.BodyModify = funcProcTrim(t)
			res = append(res, re)
		}
	}
	return
}
func dbmsJobConvert(s global.SchemaObjectInput, p global.JobResultDefinition) (res global.JobDefinitionConvertS, err error) {
	var (
		event string
	)
	if res, err = dbmsJobIntervalConvert(s, p); err != nil {
		return
	}
	res.Name = p.Name
	res.BodyPrepare = jobBodyPrepareActive(event, p.Action, s)
	//res.BodyPrepare = jobBodyPrepareActive(Event, p.Action, s)
	res.JobStatus = "TRUE"
	res.IntervalOld = p.Interval
	res.Description = p.Interval
	res.PrintInfo = jobPrint(p.Action, res.StartTime, p.Interval)
	return
}

// func jobStatusConvert
func convertJob(s global.SchemaObjectInput, p global.JobResultDefinition) (res global.JobDefinitionConvertS, err error) {
	//var q global.JobInterval
	switch p.JobPackage {
	case "DBMS_SCHEDULER.CREATE_JOB":
	case "DBMS_JOB.SUBMIT":
		return dbmsJobConvert(s, p)
	default:
		return
	}
	return
}

func (or SchemaObjectMeta) Job(s global.SchemaObjectInput) (res global.SchemaObjectReturnResult, err error) {
	var (
		definition global.JobResultDefinition
		convert    global.JobDefinitionConvertS
	)
	if definition, err = dbmsScheduler(s); err != nil || len(definition.Name) == 0 {
		if definition, err = dbmsJob(s); err != nil {
			return
		}
	}
	if convert, err = convertJob(s, definition); err != nil {
		return
	}
	definition.PrintInfo = convert.PrintInfo
	return global.SchemaObjectReturnResult{
		JobResult: global.JobObjectResult{
			Definition: definition,
			Convert:    convert,
		},
	}, err
}
